/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;

import ModelLayer.*;
import java.util.ArrayList;
import java.sql.*;

/**
 *
 * @author Claus
 */
public class DBSalesOrder implements IFDBSalesOrder
{

	private Connection con;
	private DBSalesLineItem dbLineItems;
	private DBInvoice dbInv;

	public DBSalesOrder()
	{
		con = DBConnection.getInstance().getDBcon();
		dbLineItems = new DBSalesLineItem();
		dbInv = new DBInvoice();

	}

	public int deleteSalesOrder(int salesID)
	{
		//TODO : Skal sørge for alt på denne salesOrder bliver tilbageført til stock, kræver dbproduct færdig.
		int rc = -1;

		String query = "DELETE FROM SalesOrder WHERE salesID = "
				+ salesID;

		SalesOrder order = findSalesOrder(salesID, true);

		System.out.println(query);

		try
		{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);

			for(SalesLineItem item : order.getSalesItems())
			{
				dbLineItems.deleteSalesLineItem(item.getSliID());
			}
		}
		catch (Exception ex)
		{
			System.out.println("Delete exception in salesorder db: " + ex);

			try
			{
				rc = -1;
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (Exception ex1)
			{
				System.out.println("Error rolling back" + ex1);
			}
		}
		return (rc);
	}

	public SalesOrder findSalesOrder(int id, boolean retriveAssociation)
	{
		return singleWhere("salesID = " + id, retriveAssociation);
	}

	public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}

	public ArrayList<SalesOrder> getCustomerSalesOrders(int cusID)
	{
		return miscWhere("customerID = " + cusID, false);
	}

	public ArrayList<SalesOrder> getInvoiceSalesOrders(int invID)
	{
		return miscWhere("invoiceNo = " + invID, false);
	}

	public int insertSalesOrder(SalesOrder order)
	{
		int nextID = GetMax.getMaxId("Select max(salesID) from SalesOrder");
		nextID = nextID + 1;
		System.out.println("next salesID = " + nextID);

		int rc = -1;
		String query = "INSERT INTO SalesOrder(salesID, date, deliveryStatus, deliveryDate, customerID, invoiceNo)  VALUES("
				+ nextID + ",'"
				+ order.getDate() + "','"
				+ order.getDeliveryStatus() + "','"
				+ order.getDeliveryDate() + "',"
				+ order.getCustomer().getCustomerID() + ","
				+ order.getOrderInvoice().getInvoiceNo() + ")";

		System.out.println("insert : " + query);


		try
		{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);

			for(SalesLineItem lineItem : order.getSalesItems())
			{
				dbLineItems.insertSalesLineItem(lineItem);
			}
		}
		catch (SQLException ex)
		{
			System.out.println("SalesOrder ikke oprettet");
			System.out.println("Insert exception in salesorder db: " + ex);
			try
			{
				rc = -1;
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (Exception ex1)
			{
				System.out.println("Error rolling back" + ex1);
			}
		}
		return nextID;
	}

	public int updateSalesOrder(SalesOrder order)
	{
		SalesOrder salesObj = order;

		int rc = -1;

		String query = "UPDATE SalesOrder SET "
				+ "date ='" + salesObj.getDate() + "', "
				+ "deliveryStatus ='" + salesObj.getDeliveryStatus() + "', "
				+ "deliveryDate ='" + salesObj.getDeliveryDate() + "', "
				+ "customerID =" + salesObj.getCustomer().getCustomerID() + ", "
				+ "invoiceNo = " + salesObj.getOrderInvoice().getInvoiceNo()
				+ " WHERE salesID = " + salesObj.getSalesID();

		System.out.println("Update query:" + query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}
		catch (Exception ex)
		{
			System.out.println("Update exception in customer db: " + ex);
		}
		return (rc);
	}

	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();
		String query = buildQuery(wClause);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next())
			{
				SalesOrder salesObj = new SalesOrder();
				salesObj = buildSalesOrder(results);
				list.add(salesObj);
			

				if (retrieveAssociation)
				{

					DBCustomer cusDB = new DBCustomer();
					IFDBSalesLineItem dbSalesItem = new DBSalesLineItem();
					salesObj.setCustomer(cusDB.findCustomer(results.getInt("customerID"), false));

					salesObj.setOrderInvoice(dbInv.findInvoice(Integer.parseInt(results.getString("invoiceNo")), false));
					salesObj.setSalesItems(dbSalesItem.getSalesOrderItems(results.getInt(("salesID"))));

				}
			}

			stmt.close();

		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder salesObj = new SalesOrder();

		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next())
			{
				salesObj = buildSalesOrder(results);

				if (retrieveAssociation)
				{
					DBCustomer cusDB = new DBCustomer();
					IFDBSalesLineItem dbSalesItem = new DBSalesLineItem();
					salesObj.setCustomer(cusDB.findCustomer(results.getInt("customerID"), false));

					salesObj.setOrderInvoice(dbInv.findInvoice(Integer.parseInt(results.getString("invoiceNo")), false));
					salesObj.setSalesItems(dbSalesItem.getSalesOrderItems(results.getInt(("salesID"))));

				}

				stmt.close();

			}
			else
			{
				salesObj = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return salesObj;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT salesID, date, deliveryStatus, deliveryDate, customerID, invoiceNo FROM SalesOrder";

		if (wClause.length() > 0)
		{
			query = query + " WHERE " + wClause;
		}

		return query;
	}

	private SalesOrder buildSalesOrder(ResultSet results)
	{
		SalesOrder salesObj = new SalesOrder();
		try
		{
			salesObj.setSalesID(results.getInt("salesID"));
			salesObj.setOrderInvoice(null);
			salesObj.setDeliveryStatus(results.getInt("deliveryStatus"));
			salesObj.setDeliveryDate(results.getString("deliveryDate"));
			salesObj.setDate(results.getString("date"));
			salesObj.setCustomer(null);
			salesObj.setSalesItems(null);
		}
		catch (Exception e)
		{
			System.out.println("error in building the salesOrder object");
		}
		return salesObj;
	}
}
